﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.GERP.GIMS.MODEL;
using System.Data;
using KPIS.DBM;
using System.Configuration;

namespace KPIS.GERP.GIMS.DAL
{
    public class D_IcmReceive
    {
        DBManager dbManager = new DBManager(DataProvider.MySQL, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public IList<M_IcmReceive> GetList() 
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_IcmReceive> infoList = new List<M_IcmReceive>();
            try
            {
                dbManager.Open();
                strSQL = " SELECT  res_item.icm_res_item_name,delivery_item.item_qty ,delivery_item.item_price_exclude_vat,delivery_item.item_price_include_vat,delivery_item.exclude_vat_amount  "
                         + " ,delivery_item.vat_amount,delivery_item.net_amount,pr_item.icm_res_item_seq,delivery.receive_date,delivery.invoice_number,delivery.invoice_date,po.icm_po_ref_no,delivery.icm_dn_ref_no "
                         + " FROM icm_delivery_note_item delivery_item "
                         + " LEFT JOIN icm_purchase_order_installment_item po_install_item ON delivery_item.icm_po_install_item_seq = po_install_item.icm_po_install_item_seq "
                         + " LEFT JOIN icm_bidder_quotation  bid_quo ON po_install_item.icm_bidder_quo_seq= bid_quo.icm_bidder_quo_seq "
                         + " LEFT JOIN icm_purchase_request_item pr_item ON bid_quo.icm_pr_item_seq = pr_item.icm_pr_item_seq "
                         + " LEFT JOIN icm_mas_resource_item res_item ON pr_item.icm_res_item_seq = res_item.icm_res_item_seq "
                         + " LEFT JOIN icm_delivery_note delivery ON delivery_item.icm_dn_seq = delivery.icm_dn_seq "
                         + " LEFT JOIN icm_purchase_order_installment po_install ON po_install_item.icm_po_install_seq = po_install.icm_po_install_seq "
                         + " LEFT JOIN icm_purchase_order po ON po_install.icm_po_seq = po.icm_po_seq ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_IcmReceive info = new M_IcmReceive();
                        info.icm_res_item_seq = Convert.ToInt32(drc[i]["icm_res_item_seq"]);
                        info.icm_res_item_name = drc[i]["icm_res_item_name"].ToString();
                        info.item_qty = cn.ConvertNullDouble(drc[i]["item_qty"]);
                        info.item_price_exclude_vat = cn.ConvertNullDouble(drc[i]["item_price_exclude_vat"]);
                        info.item_price_include_vat = cn.ConvertNullDouble(drc[i]["item_price_include_vat"]);
                        info.exclude_vat_amount = cn.ConvertNullDouble(drc[i]["exclude_vat_amount"]);
                        info.vat_amount = cn.ConvertNullDouble(drc[i]["vat_amount"]);
                        info.net_amount = cn.ConvertNullDouble(drc[i]["net_amount"]);
                        info.receive_date = cn.ConvertNullDateTime(drc[i]["receive_date"]);
                        info.invoice_number = drc[i]["invoice_number"].ToString();
                        info.invoice_date = cn.ConvertNullDateTime(drc[i]["invoice_date"]);
                        info.icm_po_ref_no = drc[i]["icm_po_ref_no"].ToString();
                        info.derivery_ref_no = drc[i]["icm_dn_ref_no"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_IcmReceive GetListFillBySeq(Nullable<Int32> icm_res_item_seq, Nullable<Int32> invoice_number, string icm_po_ref_no)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_IcmReceive  info = new M_IcmReceive();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@icm_res_item_seq", icm_res_item_seq);
                dbManager.AddParameters(1, "@invoice_number", invoice_number);
                dbManager.AddParameters(2, "@icm_po_ref_no", icm_po_ref_no);

                strSQL = " SELECT  res_item.icm_res_item_name,delivery_item.item_qty ,delivery_item.item_price_exclude_vat,delivery_item.item_price_include_vat,delivery_item.exclude_vat_amount  "
                         + " ,delivery_item.vat_amount,delivery_item.net_amount,pr_item.icm_res_item_seq,delivery.receive_date,delivery.invoice_number,delivery.invoice_date,po.icm_po_ref_no,delivery.icm_dn_ref_no "
                         + " FROM icm_delivery_note_item delivery_item "
                         + " LEFT JOIN icm_purchase_order_installment_item po_install_item ON delivery_item.icm_po_install_item_seq = po_install_item.icm_po_install_item_seq "
                         + " LEFT JOIN icm_bidder_quotation  bid_quo ON po_install_item.icm_bidder_quo_seq= bid_quo.icm_bidder_quo_seq "
                         + " LEFT JOIN icm_purchase_request_item pr_item ON bid_quo.icm_pr_item_seq = pr_item.icm_pr_item_seq "
                         + " LEFT JOIN icm_mas_resource_item res_item ON pr_item.icm_res_item_seq = res_item.icm_res_item_seq "
                         + " LEFT JOIN icm_delivery_note delivery ON delivery_item.icm_dn_seq = delivery.icm_dn_seq "
                         + " LEFT JOIN icm_purchase_order_installment po_install ON po_install_item.icm_po_install_seq = po_install.icm_po_install_seq "
                         + " LEFT JOIN icm_purchase_order po ON po_install.icm_po_seq = po.icm_po_seq "
                         + "  WHERE (res_item.icm_res_item_seq = @icm_res_item_seq and invoice_number = @invoice_number and icm_po_ref_no = @icm_po_ref_no)";
                         //+ " WHERE pr_item.icm_res_item_seq = @icm_res_item_seq ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.icm_res_item_seq = Convert.ToInt32(drc[0]["icm_res_item_seq"]);
                    info.icm_res_item_name = drc[0]["icm_res_item_name"].ToString();
                    info.item_qty = cn.ConvertNullDouble(drc[0]["item_qty"]);
                    info.item_price_exclude_vat = cn.ConvertNullDouble(drc[0]["item_price_exclude_vat"]);
                    info.item_price_include_vat = cn.ConvertNullDouble(drc[0]["item_price_include_vat"]);
                    info.exclude_vat_amount = cn.ConvertNullDouble(drc[0]["exclude_vat_amount"]);
                    info.vat_amount = cn.ConvertNullDouble(drc[0]["vat_amount"]);
                    info.net_amount = cn.ConvertNullDouble(drc[0]["net_amount"]);
                    info.receive_date = cn.ConvertNullDateTime(drc[0]["receive_date"]);
                    info.invoice_number = drc[0]["invoice_number"].ToString();
                    info.invoice_date = cn.ConvertNullDateTime(drc[0]["invoice_date"]);
                    info.derivery_ref_no = drc[0]["icm_dn_ref_no"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public M_IcmReceive GenerateTransCode()
        {
             string strSQL;
            DataRowCollection drc = null;
            M_IcmReceive  info = new M_IcmReceive();
            try
            {
                dbManager.Open();

                strSQL = " SELECT case when  (Year(Now()) <2500) then Year(Now())+543 else Year(Now()) end YearValue , Month(Now()) as MonthValue,COUNT(trans_mas_seq) as running "
                         + " FROM icm_trans_master "
                         + " WHERE YEAR(created_when) = YEAR(Now()) and "
                         + " Month(created_when) = Month(Now()) ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.stryear = drc[0]["YearValue"].ToString();
                    info.strmonth = drc[0]["MonthValue"].ToString();
                    info.transcode_running = drc[0]["running"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public Int32 GetLotNumber(Int32 icm_res_item_seq)
        {
            string strSQL;
            Int32 intlot_number =0;
            DataRowCollection drc = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@icm_res_item_seq", icm_res_item_seq);

                strSQL = " SELECT  count(icm_res_item_seq)+1 as lot_number "
                        +" FROM icm_receive "
                        +" WHERE icm_res_item_seq = @icm_res_item_seq ";
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    intlot_number = Convert.ToInt32(drc[0]["lot_number"]);
                }

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return intlot_number;
        }

        public void Insert_Icm_Receive(M_IcmReceive info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(16);
                dbManager.AddParameters(0, "@trans_mas_seq", info.trans_mas_seq );
                dbManager.AddParameters(1, "@lot_number", info.lot_number);
                dbManager.AddParameters(2, "@icm_res_item_seq", info.icm_res_item_seq);
                dbManager.AddParameters(3, "@date_expired", info.date_expired);
                dbManager.AddParameters(4, "@auto_transfer_organize_flag", info.auto_transfer_organize_flag);
                dbManager.AddParameters(5, "@quantity", info.quantity);
                dbManager.AddParameters(6, "@price", info.price);
                dbManager.AddParameters(7, "@quantity_remain_before", info.quantity_remain_before);
                dbManager.AddParameters(8, "@quantity_remain_after", info.quantity_remain_after);
                dbManager.AddParameters(9, "@quantity_remain", info.quantity_remain);
                dbManager.AddParameters(10, "@receive_date", info.receive_date);
                dbManager.AddParameters(11, "@schedule_receive_date", info.schedule_receive_date);
                dbManager.AddParameters(12, "@remark", info.remark);
                dbManager.AddParameters(13, "@record_status", info.record_status);
                dbManager.AddParameters(14, "@created_by", info.created_by);
                dbManager.AddParameters(15, "@created_when", info.created_when);

                strSQL = " INSERT INTO icm_receive "
                        + " (trans_mas_seq,lot_number,icm_res_item_seq,date_expired "
                        + " ,auto_transfer_organize_flag,quantity,price,quantity_remain_before "
                        + " ,quantity_remain_after,quantity_remain,receive_date,schedule_receive_date,remark "
                        + " ,record_status,created_by,created_when) "
                        + "  VALUES "
                        + " (@trans_mas_seq "
                        + " ,@lot_number "
                        + " ,@icm_res_item_seq "
                        + " ,@date_expired "
                        + " ,@auto_transfer_organize_flag "
                        + " ,@quantity "
                        + " ,@price "
                        + " ,@quantity_remain_before "
                        + " ,@quantity_remain_after "
                        + " ,@quantity_remain "
                        + " ,@receive_date "
                        + " ,@schedule_receive_date "
                        + " ,@remark "
                        + " ,@record_status "
                        + " ,@created_by "
                        + " ,@created_when) ";
                
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update_Icm_Receive(M_IcmReceive  info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(16);
                dbManager.AddParameters(0, "@icm_rc_seq", info.icm_rc_seq);
                dbManager.AddParameters(1, "@trans_mas_seq", info.trans_mas_seq);
                dbManager.AddParameters(2, "@lot_number", info.lot_number);
                dbManager.AddParameters(3, "@icm_res_item_seq", info.icm_res_item_seq);
                dbManager.AddParameters(4, "@date_expired", info.date_expired);
                dbManager.AddParameters(5, "@auto_transfer_organize_flag", info.auto_transfer_organize_flag);
                dbManager.AddParameters(6, "@quantity", info.quantity);
                dbManager.AddParameters(7, "@price", info.price);
                dbManager.AddParameters(8, "@quantity_remain_before", info.quantity_remain_before);
                dbManager.AddParameters(9, "@quantity_remain_after", info.quantity_remain_after);
                dbManager.AddParameters(10, "@quantity_remain", info.quantity_remain);
                dbManager.AddParameters(11, "@receive_date", info.receive_date);
                dbManager.AddParameters(12, "@schedule_receive_date", info.schedule_receive_date);
                dbManager.AddParameters(13, "@record_status", info.record_status);
                dbManager.AddParameters(14, "@updated_by", info.updated_by);
                dbManager.AddParameters(15, "@updated_when", info.updated_when);

                strSQL =   " UPDATE  icm_receive "
                         + " SET trans_mas_seq = @trans_mas_seq "
                         + " lot_number = @lot_number "
                         + " icm_res_item_seq = @icm_res_item_seq "
                         + " date_expired = @date_expired "
                         + " auto_transfer_organize_flag = @auto_transfer_organize_flag "
                         + " quantity = @quantity "
                         + " price = @price "
                         + " quantity_remain_before = @quantity_remain_before "
                         + " quantity_remain_after = @quantity_remain_after "
                         + " quantity_remain = @quantity_remain "
                         + " remark = @remark "
                         + " receive_date = @receive_date "
                         + " schedule_receive_date = @schedule_receive_date "
                         + " record_status = @record_status "
                         + " updated_by = @updated_by "
                         + " updated_when = @updated_when ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }


    }
}
